<?php
namespace app\manage\controller;
use think\Db;

class AuditSdkMng extends Base
{
    //联运对账列表
    public function index(){
        if(request()->isAjax()){
            $page = input('page');
            $limit = input('limit');
            $id = input('id');
            $channelId = input('channel_id');
            $date = input('receipt_time');
            $balanceType = input('balance_type');
            if( !empty($date) ){
                $arr = explode(' - ', $date);
                $receiptTime['start_time'] = strtotime($arr[0]);
                $receiptTime['end_time'] = strtotime($arr[1])+86399;
            }else{
                $receiptTime = false;
            }

            $where = [];
            !empty($id) && $where['id'] = $id;
            !empty($channelId) && $where['channel_id'] = $channelId;
            !empty($balanceType) && $where['balance_type'] = $balanceType;
            !empty($receiptTime) && $where['receipt_time'] = ['between', [$receiptTime['start_time'], $receiptTime['end_time']]];

            if( ! $count=model('SdkBill')->where($where)->count() ){
               $this->layTable();
            }

            $select = model('SdkBill')
                ->where($where)
                ->limit(($page-1)*$limit, $limit)
                ->order('id desc')
                ->select();

            $data = [];
            $balanceType = config('balance_type');
            $channelIdName = changeValKey('Channel', 'channel_id', 'channel_name');
            foreach ($select as $k=>$v){
                $data[] = [
                    'id' => $v['id'],
                    'balance_type' => $balanceType[ $v['balance_type'] ],
                    'daytime' => $v['start_time'].' - '.$v['end_time'],
                    'channel_id' => $v['channel_id'].'：'.$channelIdName[ $v['channel_id'] ],
                    'game_name' => $v['game_name'],
                    'other_fixed_price' => $v['other_fixed_price'],
                    'sdk_fixed_price' => $v['sdk_fixed_price'],
                    'diff_price' => round($v['other_fixed_price']-$v['sdk_fixed_price'], 2),
                    'pay_rate' => $v['pay_rate']. '%',
                    'pay_price' => $v['other_total_price'],
                    'channel_rate' => $v['channel_rate'].'%',
                    'tax_rate' => $v['tax_rate'].'%',
                    'balance_time' => $v['balance_time'],
                    'receipt_time' => $v['receipt_time'],
                    'receipt_tag' => $v['receipt_tag'],
                    'receipt_price' => $v['receipt_price'],
                    'open_invoice' => $v['open_invoice'] ? '是' : '',
                    'send_time' => $v['send_time'],
                    'send_info' => $v['send_info'],
                    'company' => $v['company'],
                    'status' => $v['status'],
                    'urge' => $v['urge'],
                ];
            }

            $this->layTable(0, '礼包列表', $data, $count);
        }else{
            return view();
        }
    }

    //获取渠道已生成的最终对账时间
    public function time(){
        $where['channel_id'] = input('channel_id');
        $where['balance_type'] = input('balance_type');
        $endTime = model('SdkBill')->where($where)->order('end_time desc')->value('end_time');
        return $endTime ? $this->layJson(0, '', date('Y-m-d', $endTime+1)) : $this->layJson(0, '', '2016-01-01');
    }

    //查询金额
    public function select(){
        $channelId = input('channel_id');
        $date['start_time'] = strtotime(input('date'));
        $date['end_time'] = strtotime("+1 months", strtotime(input('date')))-1;
        $invoiceType = input('invoice_type');
        $balanceType = input('balance_type');

        $whereEdit['channel_id'] = $channelId;
        $whereEdit['balance_type'] = $balanceType;
        if($daytime = model('SdkBill')->field('start_time, end_time')->where($whereEdit)->select()){
            foreach ($daytime as $k=>$v){
                if(($date['start_time']>=strtotime($v['start_time']) && $date['start_time']<=strtotime($v['end_time'])+86399) || ($date['end_time']>=strtotime($v['start_time']) && $date['end_time']<=strtotime($v['end_time'])+86399)){
                    $this->layJson(1, "{$channelId} 渠道号存在日期重叠的账单");
                }
            }
        }

        $where = "pay_time between '{$date['start_time']}' AND '{$date['end_time']}' && debug='0' && pay_result='1' && channel_id='{$channelId}' && channel_id>=5000";

        //跨年分表
        $y1 = date('Y', $date['start_time']);
        $y2 = date('Y', $date['end_time']);
        if($y1 == $y2){
            $sdk_fixed_price = Db::name('PayOrderY_'.$y1)->where($where)->sum('total_price');
        }else{
            $field = "total_price";

            $subQuery = Db::name('PayOrderY_'.$y1)
                ->field($field)
                ->where($where)
                ->union("select {$field} from sdk_pay_order_y_{$y2} where ({$where})", true)
                ->buildSql();
            $sdk_fixed_price = Db::table($subQuery.' y')->sum('total_price');
        }

        $channel = model('Channel')->field('channel_rate, pay_rate, tax_rate, company, reg_address, contact_phone, contact_man, open_name, open_bank, open_account, credit_code, send_address, contact_mobile')->where('channel_id', $channelId)->find();

        $data['pay_rate'] = $channel['pay_rate'];
        $data['tax_rate'] = $channel['tax_rate'];
        $data['channel_rate'] = $channel['channel_rate'];

        $data['sdk_fixed_price'] = round($sdk_fixed_price/100, 2);
        $data['all_total_price'] = round($data['sdk_fixed_price']*($data['pay_rate']/100)*(1-($data['channel_rate']/100))*(1-($data['tax_rate']/100)), 2);
        $data['company'] = $channel['company'];
        $data['reg_address'] = $channel['reg_address'];
        $data['contact_phone'] = $channel['contact_phone'];
        $data['contact_man'] = $channel['contact_man'];
        $data['open_name'] = $channel['open_name'];
        $data['open_bank'] = $channel['open_bank'];
        $data['open_account'] = $channel['open_account'];
        $data['credit_code'] = $channel['credit_code'];
        $data['send_address'] = $channel['send_address'];
        $data['contact_mobile'] = $channel['contact_mobile'];

        $this->layJson(0, '', $data);
    }

    //添加
    public function add(){
        if( input('channel_id') ){
            $data = input();
            $data['start_time'] = strtotime(input('date'));
            $data['end_time'] = strtotime("+1 months", strtotime(input('date')))-1;
            unset($data['date']);
            $data['status'] = 1;
            $data['other_fixed_price'] = $data['sdk_fixed_price'];

            $whereEdit['channel_id'] = $data['channel_id'];
            $whereEdit['balance_type'] = $data['balance_type'];
            if($daytime = model('SdkBill')->field('start_time, end_time')->where($whereEdit)->select()){
                foreach ($daytime as $k=>$v){
                    if(($data['start_time']>=strtotime($v['start_time']) && $data['start_time']<=strtotime($v['end_time'])+86399) || ($data['end_time']>=strtotime($v['start_time']) && $data['end_time']<=strtotime($v['end_time'])+86399)){
                        $this->layJson(1, "{$data['channel_id']} 渠道号存在日期重叠的账单");
                    }
                }
            }

            //生成总表数据
            if( model('SdkBill')->save($data) === false ){
               $this->layJson(1, '生成结算单失败，请重试');
            }

            $find = model('SdkBill')->where('id', model('SdkBill')->id)->find();
            $info = $find->append(['channel_name'])->toArray();

            //生成详情表数据
            $whereSdk = "pay_time between '{$data['start_time']}' AND '{$data['end_time']}' && debug='0' && pay_result='1' && channel_id='{$data['channel_id']}' && channel_id>=5000";
            $selectSdk = Db::name('PayOrderY_'.date('Y', $data['start_time']))
                ->field('game_id, sum(total_price) as total_price')
                ->where($whereSdk)
                ->group('game_id')
                ->select();

            $billDetail = [];
            $allTotalPrice = 0;
            foreach ($selectSdk as $k=>$v){
                //如果自定义比例，则按自定义比例算
                $whereRate['channel_id'] = $info['channel_id'];
                $whereRate['game_id'] = $v['game_id'];
                if( $billRate = model('SdkBillRate')->where($whereRate)->find()){
                    $rate['pay_rate'] = $billRate['pay_rate'];
                    $rate['tax_rate'] = $billRate['tax_rate'];
                    $rate['channel_rate'] = $billRate['channel_rate'];
                }else{
                    $rate['pay_rate'] = $info['pay_rate'];
                    $rate['tax_rate'] = $info['tax_rate'];
                    $rate['channel_rate'] = $info['channel_rate'];
                }

                $payPrice = round(($v['total_price']/100)*($rate['pay_rate']/100)*(1-$rate['tax_rate']/100)*(1-$rate['channel_rate']/100), 2);

                $billDetail[] = [
                    'bill_id' => $info['id'],
                    'start_time' => strtotime($info['start_time']),
                    'end_time' => strtotime($info['end_time'])+86399,
                    'status' => 0,
                    'total_price' => $v['total_price']/100,
                    'game_id' => $v['game_id'],
                    'channel_id' => $info['channel_id'],
                    'channel_rate' => $rate['channel_rate'],
                    'tax_rate' => $rate['tax_rate'],
                    'pay_rate' => $rate['pay_rate'],
                    'pay_price' => $payPrice,
                ];

                $allTotalPrice += $payPrice;
            }
            //添加到详情表和修改总表需回款金额
            if(model('SdkBillDetail')->saveAll($billDetail)===false || model('SdkBill')->save(['all_total_price'=>$allTotalPrice, 'other_total_price'=>$allTotalPrice], ['id'=>$info['id']])===false){
                $this->layJson(1, '生成结算单失败，请联系管理员');
            }

            $this->layJson(0, '提交成功');
        }else{
            return view();
        }
    }

    //导出结算单
    public function export2(){
        $data = input();
        $data['start_time'] = strtotime(input('date'));
        $data['end_time'] = strtotime("+1 months", strtotime(input('date')))-1;

        if($data['invoice_content']){
            $invoiceContent = config('invoice_content');
            $data['invoice_content'] = $invoiceContent[ $data['invoice_content'] ];
        }

        $whereSdk = "pay_time between '{$data['start_time']}' AND '{$data['end_time']}' && debug='0' && pay_result='1' && channel_id='{$data['channel_id']}' && channel_id>=5000";
        $selectSdk = Db::name('PayOrderY_'.date('Y', $data['start_time']))
            ->field('game_id, sum(total_price) as total_price')
            ->where($whereSdk)
            ->group('game_id')
            ->select();

        $channelIdName = model('Channel')->channelIdName();
        $gameIdName = model('Game')->gameIdName();
        $order = [];

        if( $selectSdk ){
            foreach($selectSdk as $v){
                //如果自定义比例，则按自定义比例算
                $whereRate['channel_id'] = $data['channel_id'];
                $whereRate['game_id'] = $v['game_id'];
                if( $billRate = model('SdkBillRate')->where($whereRate)->find()){
                    $rate['pay_rate'] = $billRate['pay_rate'];
                    $rate['tax_rate'] = $billRate['tax_rate'];
                    $rate['channel_rate'] = $billRate['channel_rate'];
                }else{
                    $rate['pay_rate'] = $data['pay_rate'];
                    $rate['tax_rate'] = $data['tax_rate'];
                    $rate['channel_rate'] = $data['channel_rate'];
                }

                $order[] = [
                    'date' => date('Y年m月', $data['start_time']),
                    'channel_name' => $channelIdName[ $data['channel_id'] ],
                    'game_id' => $v['game_id'],
                    'game_name' => $gameIdName[ $v['game_id'] ],
                    'total_price' => $v['total_price']/100,
                    'channel_rate' => $rate['channel_rate'] . '%',
                    'tax_rate' => $rate['tax_rate'] . '%',
                    'pay_rate' => $rate['pay_rate'] . '%',
                    'pay_receipt' => 'dd',
                ];
            }
        }else{
            $order[] = [
                'date' => date('Y年m月', $data['start_time']),
                'channel_name' => $channelIdName[ $data['channel_id'] ],
                'game_id' => 0,
                'game_name' => 0,
                'total_price' => 0,
                'channel_rate' => $data['channel_rate'] . '%',
                'tax_rate' => $data['tax_rate'] . '%',
                'pay_rate' => $data['pay_rate'] . '%',
                'pay_receipt' => 'dd',
            ];
        }


        //生成结算单 米娱bt-cp-流水结算单
        $excelName = $channelIdName[ $data['channel_id'] ].input('date').'月';
        $this->exportExcel($data, $order, $excelName, $excelName);
    }

    //编辑
    public function edit(){
        if( input('channel_id') ){
            $data['id'] = input('id');
            $data['company'] = input('company');
            $data['invoice_content'] = input('invoice_content');
            $data['reg_address'] = input('reg_address');
            $data['contact_phone'] = input('contact_phone');
            $data['contact_man'] = input('contact_man');
            $data['open_name'] = input('open_name');
            $data['open_bank'] = input('open_bank');
            $data['open_account'] = input('open_account');
            $data['credit_code'] = input('credit_code');
            $data['game_name'] = input('game_name');
            $data['send_address'] = input('send_address');
            $data['remark'] = input('remark');

            if( model('SdkBill')->isUpdate(true)->save($data)===false ){
                $this->layJson(1, '生成结算单失败，请重试');
            }

            $this->layJson(0, '修改成功');
        }else{
            $find = model('SdkBill')->find( input('id') );
            $find['date'] = date('Y-m', strtotime($find['start_time']));
            return view('', ['find'=>$find]);
        }
    }

    //删除
    public function del(){
        $rs1 = model('SdkBill')->destroy(input('id'));
        $rs2 = model('SdkBillDetail')->where('bill_id', input('id'))->delete();
        if($rs1!==false && $rs2!==false){
            $this->layJson(0, '删除成功');
        }else{
            $this->layJson(1, '删除失败，请重试');
        }
    }

    //查看
    public function view(){
        if(request()->isAjax()){
            $page = input('page');
            $limit = input('limit');
            $id = input('id');

            if( ! $count=model('SdkBillDetail')->where('bill_id', $id)->count() ){
                $this->layTable();
            }

            $find = model('SdkBill')->where('id', $id)->find();
            $select = model('SdkBillDetail')
                ->where('bill_id', $id)
                ->order('id desc')
                ->limit(($page-1)*$limit, $limit)
                ->select();

            $gameIdName = model('Game')->gameIdName();
            $billDetail = [];
            foreach ($select as $k=>$v){
                $billDetail[] = [
                    'date' => date('Y-m', strtotime($find['start_time'])),
                    'channel_name' => $find['channel_id'],
                    'game_id' => $v['game_id'],
                    'game_name' => $gameIdName[ $v['game_id'] ],
                    'total_price' => $v['total_price'],
                    'channel_rate' => $find['channel_rate'].'%',
                    'tax_rate' => $find['tax_rate'].'%',
                    'pay_rate' => $find['pay_rate'].'%',
                    'pay_price' => round($v['total_price']*($find['pay_rate']/100)*(1-$find['tax_rate']/100)*(1-$find['channel_rate']/100), 2),
                ];
            }

            $this->layTable(0, '礼包列表', $billDetail, $count);
        }else{
            return view();
        }
    }

    //填写
    public function info(){
        if(request()->isAjax()){
            $data = input();
            $data['open_invoice'] = isset($data['open_invoice']) ? 1 : 0;

            if( model('SdkBill')->isUpdate(true)->save($data)===false ){
                $this->layJson(1, '填写信息失败，请重试');
            }

            $this->layJson(0, '提交成功');
        }else{
            $find = model('SdkBill')->where('id', input('id'))->find();
            $bill = $find->append(['channel_name'])->toArray();
            return view('', ['bill'=>$bill]);
        }
    }

    //已核对
    public function receive(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 1;
        $idArr = model('SdkBill')->where($where)->column('id');
        $update = [];
        foreach ($idArr as $k=>$v){
            $update[] = [
                'id' => $v,
                'status' => 2
            ];
        }
        model('SdkBill')->saveAll($update) ? $this->layJson(0, '修改成功') : $this->layJson(1, '修改失败，请重试');
    }

    //已回款
    public function finish(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 2;
        $bill = model('SdkBill')->field('id,channel_id,end_time')->where($where)->select();

        try{
            foreach ($bill as $k=>$v){
                //修改账单状态
                Db::name('SdkBill')->where('id', $v['id'])->update([
                    'status' => 3,
                    'receipt_time' => time(),
                    'receipt_tag' => 0,
                ]);

                //修改账单详情状态
                Db::name('SdkBillDetail')->where('bill_id', $v['id'])->update(['status'=>1]);

                //修改渠道最后对账时间
                Db::name('Channel')->where('channel_id', $v['channel_id'])->update(['audit_time'=>time()]);
            }
        } catch (\Exception $e){
            Db::rollback();
            $this->layJson(1, $e->getMessage());
        }

        $this->layJson(0, '修改成功');
    }

    //催单
    public function urge(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 2;
        $idArr = model('SdkBill')->where($where)->column('id');
        $update = [];
        foreach ($idArr as $k=>$v){
            $update[] = [
                'id' => $v,
                'urge' => 1
            ];
        }
        model('SdkBill')->saveAll($update) ? $this->layJson(0, '修改成功') : $this->layJson(1, '修改失败，请重试');
    }

    //导出总表
    public function export(){
        $channelId = input('channel_id');
        $date = input('transfer_time');
        $balanceType = input('balance_type');

        if( !empty($date) ){
            $arr = explode(' - ', $date);
            $transferTime['start_time'] = strtotime($arr[0]);
            $transferTime['end_time'] = strtotime($arr[1])+86399;
        }else{
            $transferTime = false;
        }

        $where = [];
        !empty($channelId) && $where['channel_id'] = $channelId;
        !empty($transferTime) && $where['transfer_time'] = ['between', [$transferTime['start_time'], $transferTime['end_time']]];
        !empty($balanceType) && $where['balance_type'] = $balanceType;
        $select = model('SdkBill')->where($where)->order('transfer_time desc')->select();

        !$select && $this->error('数据为空，请重新赛选条件');

        $collect = collection($select)->append(['game_name'])->toArray();

        foreach ($collect as $k=>$v){
            if($v['server_price']!=0){ //服务器费用
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['channel_id'],
                    $v['game_name'],
                    '服务器费用',
                    $v['server_price'],
                    $v['pay_rate'],
                    $v['server_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['all_total_price']
                ];
            }else{
                //CPS
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['channel_id'],
                    $v['game_name'],
                    'CPS',
                    $v['cps_pay_price'],
                    $v['pay_rate'],
                    $v['cps_pay_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['cps_total_price']
                ];
                //联运
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['channel_id'],
                    $v['game_name'],
                    '联运',
                    $v['sdk_pay_price'],
                    $v['pay_rate'],
                    $v['sdk_pay_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['sdk_total_price']
                ];
            }
        }
        array_unshift($data, ['结账起始时间', '结账终点时间', '游戏号', '游戏名称', '类型', '总金额（元）', '分成比例', '金额（元）', '通道费', '税点', '实结金额（元）']);
        exportExcel($data, $date.'转账', $date.'转账');
    }

    //导出Excel表格
    private function exportExcel($bill, $order, $excelFileName,$sheetTitle){
        import("php_excel.PHPExcel");

        /* 实例化类 */
        $objPHPExcel = new \PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /* sheet标题 */
        $objActSheet->setTitle($sheetTitle);

        $objActSheet->getColumnDimension('A')->setWidth(15);
        $objActSheet->getColumnDimension('B')->setWidth(17);
        $objActSheet->getColumnDimension('C')->setWidth(11);
        $objActSheet->getColumnDimension('D')->setWidth(17);
        $objActSheet->getColumnDimension('E')->setWidth(16);
        $objActSheet->getColumnDimension('F')->setWidth(14);
        $objActSheet->getColumnDimension('G')->setWidth(14);
        $objActSheet->getColumnDimension('H')->setWidth(16);
        $objActSheet->getColumnDimension('I')->setWidth(19);

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);

        $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '结算单'); //设置文字
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->setCellValue('A2', '结算周期');
        $objPHPExcel->getActiveSheet()->setCellValue('B2', '平台');
        $objPHPExcel->getActiveSheet()->setCellValue('C2', '游戏ID');
        $objPHPExcel->getActiveSheet()->setCellValue('D2', '游戏名称');
        $objPHPExcel->getActiveSheet()->setCellValue('E2', '充值金额');
        $objPHPExcel->getActiveSheet()->setCellValue('F2', '通道费率');
        $objPHPExcel->getActiveSheet()->setCellValue('G2', '税率');
        $objPHPExcel->getActiveSheet()->setCellValue('H2', '甲方分成比例');
        $objPHPExcel->getActiveSheet()->setCellValue('I2', '甲方分成收入');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中

        $i = 3;
        foreach($order as $value){
            $j = 'A';
            foreach($value as $value2){
                $objPHPExcel->getActiveSheet()->setCellValue($j.$i, $value2);
                if($j == 'I'){
                    $objPHPExcel->getActiveSheet()->setCellValue($j.$i, "=round(E{$i}*H{$i}*(1-F{$i})*(1-G{$i}),2)");
                }

                $objPHPExcel->getActiveSheet()->getStyle($j.$i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

                $j++;
            }
            $i++;
        }

        $line0 = $i-1;
        $line1 = $i;
        $line2 = $i+1;
        $line3 = $i+2;
        $line4 = $i+3;
        $line5 = $i+4;
        $line6 = $i+5;
        $line7 = $i+6;
        $line8 = $i+7;
        $line9 = $i+8;
        $line10 = $i+9;
        $line11 = $i+10;
        $line12 = $i+11;
        $line13 = $i+12;
        $line14 = $i+13;
        $line15 = $i+14;
        $line16 = $i+15;
        $line17 = $i+16;
        $line18 = $i+17;
        $objPHPExcel->getActiveSheet()->setCellValue("D{$line1}", '总计');
        $objPHPExcel->getActiveSheet()->setCellValue("E{$line1}", "=SUM(E3:E{$line0})");
        $objPHPExcel->getActiveSheet()->setCellValue("I{$line1}", "=SUM(I3:I{$line0})");
        $objPHPExcel->getActiveSheet()->getStyle("D{$line1}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("E{$line1}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("I{$line1}")->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line2}:C{$line2}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line2}", '支 付 金 额（大 写）：');
        $objPHPExcel->getActiveSheet()->mergeCells("D{$line2}:I{$line2}");
        $objPHPExcel->getActiveSheet()->setCellValue("D{$line2}", '=IF(ROUND(I'.$line1.',2)<0,"无效数值",IF(ROUND(I'.$line1.',2)=0,"零",IF(ROUND(I'.$line1.',2)<1,"",TEXT(INT(ROUND(I'.$line1.',2)),"[dbnum2]")&"元")&IF(INT(ROUND(I'.$line1.',2)*10)-INT(ROUND(I'.$line1.',2))*10=0,IF(INT(ROUND(I'.$line1.',2))*(INT(ROUND(I'.$line1.',2)*100)-INT(ROUND(I'.$line1.',2)*10)*10)=0,"","零"),TEXT(INT(ROUND(I'.$line1.',2)*10)-INT(ROUND(I'.$line1.',2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(I'.$line1.',2)*100)-INT(ROUND(I'.$line1.',2)*10)*10)=0,"整",TEXT((INT(ROUND(I'.$line1.',2)*100)-INT(ROUND(I'.$line1.',2)*10)*10),"[dbnum2]")&"分")))');
        $objPHPExcel->getActiveSheet()->getStyle("A{$line2}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("D{$line2}")->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line3}:I{$line3}");

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line4}:C{$line4}");
        $objPHPExcel->getActiveSheet()->mergeCells("D{$line4}:I{$line4}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line4}", '经核对，以上数据金额确认无误。');
        $objPHPExcel->getActiveSheet()->getStyle("A{$line4}")->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line5}:I{$line5}");

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line6}:E{$line6}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line7}:E{$line7}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line8}:E{$line8}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line9}:E{$line9}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line10}:E{$line10}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line11}:E{$line11}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line12}:E{$line12}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line13}:E{$line13}");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$line14}:E{$line14}");

        $objPHPExcel->getActiveSheet()->mergeCells("F{$line6}:I{$line6}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line7}:I{$line7}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line8}:I{$line8}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line9}:I{$line9}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line10}:I{$line10}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line11}:I{$line11}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line12}:I{$line12}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line13}:I{$line13}");
        $objPHPExcel->getActiveSheet()->mergeCells("F{$line14}:I{$line14}");

        $objPHPExcel->getActiveSheet()->setCellValue("A{$line6}", "乙方：{$bill['company']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line7}", "开票内容：{$bill['invoice_content']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line8}", "注册地址：{$bill['reg_address']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line9}", "注册电话：{$bill['contact_phone']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line10}", "联系人：{$bill['contact_man']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line11}", "开户名称：{$bill['open_name']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line12}", "开户银行：{$bill['open_bank']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line13}", "开户账号：{$bill['open_account']}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line14}", "统一社会信用代码：{$bill['credit_code']}");

        $objPHPExcel->getActiveSheet()->setCellValue("F{$line6}", '甲方(收款方)：上海米娱网络科技有限公司（确认盖章）');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line7}", '邮寄地址：广州市中山大道西307号东岳财富广场C座603房');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line8}", '联系人：廖晓珠');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line9}", '联系电话：13640697938');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line10}", '开户名称：上海米娱网络科技有限公司');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line11}", '开户银行:中国银行上海市闸北支行');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line12}", '开户帐号：457271932413');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line13}", '');
        $objPHPExcel->getActiveSheet()->setCellValue("F{$line14}", '开票性质：6%的增值税专用发票');

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line15}:I{$line15}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line15}", '请填写并核对好贵司银行信息、结算信息、签字、盖公章或财务章！');

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line16}:I{$line16}");

        $objPHPExcel->getActiveSheet()->mergeCells("A{$line17}:I{$line17}");
        $objPHPExcel->getActiveSheet()->setCellValue("A{$line17}", "结算单邮寄地址：{$bill['send_address']} 收件人：{$bill['contact_man']} 联系电话：{$bill['contact_phone']}");

        $objPHPExcel->getActiveSheet()->getStyle("A1:I{$line17}")->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle("D{$line1}")->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle("E{$line1}")->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle("I{$line1}")->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle("A2{$line2}")->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle("D2{$line2}")->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle("A{$line6}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line7}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line11}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line12}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line13}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line14}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("F{$line6}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("F{$line10}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("F{$line11}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("F{$line12}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("F{$line14}")->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle("A{$line15}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line17}")->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle("D{$line1}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle("E{$line1}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle("I{$line1}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle("A{$line2}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle("D{$line2}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle("A{$line17}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中

        $objPHPExcel->getActiveSheet()->getRowDimension("{$line1}")->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension("{$line2}")->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension("{$line17}")->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line1}")->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle("A{$line7}")->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line14}")->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
        $objPHPExcel->getActiveSheet()->getStyle("A{$line15}")->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_DARKBLUE);

//        $objPHPExcel->getActiveSheet()->getStyle( 'A2:I2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
//        $objPHPExcel->getActiveSheet()->getStyle( 'A2:I2')->getFill()->getStartColor()->setARGB('FF808080');

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('H2')->getFill()->getStartColor()->setARGB('FFCAE8EA');
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('I2')->getFill()->getStartColor()->setARGB('FFCAE8EA');

        #FFF000
//        $objPHPExcel->getActiveSheet()->getStyle('A13')->getAlignment()->setWrapText(true);
//        $objPHPExcel->getActiveSheet()->getStyle('E13')->getAlignment()->setWrapText(true);
//        $objPHPExcel->getActiveSheet()->mergeCells('A2:B2'); //合并单元格
//        $objPHPExcel->getActiveSheet()->setCellValue('A2', '结算月份');
//        $objPHPExcel->getActiveSheet()->setCellValue('C2', '充值金额');
//        $objPHPExcel->getActiveSheet()->setCellValue('D2', '渠道费率');
//        $objPHPExcel->getActiveSheet()->setCellValue('E2', '税率');
//        $objPHPExcel->getActiveSheet()->setCellValue('F2', '结算比例');
//        $objPHPExcel->getActiveSheet()->setCellValue('G2', '结算金额(元)');
//        $objPHPExcel->getActiveSheet()->mergeCells('A3:A4'); //合并单元格
//        $objPHPExcel->getActiveSheet()->setCellValue('A3', date('Y年m月', strtotime($bill['start_time'])));
//        $objPHPExcel->getActiveSheet()->setCellValue('B3', 'CPS数据');
//        $objPHPExcel->getActiveSheet()->setCellValue('B4', '联运数据');
//        $objPHPExcel->getActiveSheet()->setCellValue('C3', $bill['cps_pay_price']);
//        $objPHPExcel->getActiveSheet()->setCellValue('C4', $bill['sdk_pay_price']);
//        $objPHPExcel->getActiveSheet()->setCellValue('D3', $bill['channel_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('D4', $bill['channel_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('E3', $bill['tax_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('E4', $bill['tax_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('F3', $bill['pay_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('F4', $bill['pay_rate']);
//        $objPHPExcel->getActiveSheet()->setCellValue('G3', '=round(C3*F3*(1-D3)*(1-E3),2)');
//        $objPHPExcel->getActiveSheet()->setCellValue('G4', '=round(C4*F4*(1-D4)*(1-E4),2)');
//        $objPHPExcel->getActiveSheet()->mergeCells('A5:F5'); //合并单元格
//        $objPHPExcel->getActiveSheet()->setCellValue('A5', '合计');
//        $objPHPExcel->getActiveSheet()->setCellValue('G5', '=round(SUM(G3:G4),2)');
//        $objPHPExcel->getActiveSheet()->mergeCells('A6:B6');
//        $objPHPExcel->getActiveSheet()->setCellValue('A6', '支付金额(大写): ');
//        $objPHPExcel->getActiveSheet()->mergeCells('C6:G6');
//        $objPHPExcel->getActiveSheet()->setCellValue('C6', '=IF(ROUND(G5,2)<0,"无效数值",IF(ROUND(G5,2)=0,"零",IF(ROUND(G5,2)<1,"",TEXT(INT(ROUND(G5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(G5,2)*10)-INT(ROUND(G5,2))*10=0,IF(INT(ROUND(G5,2))*(INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(G5,2)*10)-INT(ROUND(G5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10)=0,"整",TEXT((INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10),"[dbnum2]")&"分")))');
//        $objPHPExcel->getActiveSheet()->mergeCells('A7:B7');
//        $objPHPExcel->getActiveSheet()->setCellValue('A7', '备注:');
//        $objPHPExcel->getActiveSheet()->mergeCells('C7:G7');
//        $objPHPExcel->getActiveSheet()->setCellValue('C7', '结算金额=充值金额*结算比例%*（1-渠道费率）*（1-税率）');
//
//        $objPHPExcel->getActiveSheet()->mergeCells('A9:D9');
//        $objPHPExcel->getActiveSheet()->setCellValue('A9', '甲方：上海米娱网络科技有限公司');
//        $objPHPExcel->getActiveSheet()->mergeCells('A10:D10');
//        $objPHPExcel->getActiveSheet()->setCellValue('A10', '盖章：');
//        $objPHPExcel->getActiveSheet()->mergeCells('A11:D11');
//        $objPHPExcel->getActiveSheet()->setCellValue('A11', '发票类型：'.$bill['invoice_type']);
//        $objPHPExcel->getActiveSheet()->mergeCells('A12:D12');
//        $objPHPExcel->getActiveSheet()->setCellValue('A12', '开票项目:信息服务费');
//        $objPHPExcel->getActiveSheet()->mergeCells('A13:D13');
//        $objPHPExcel->getActiveSheet()->setCellValue('A13', '地址：广州市天河区中山大道西路299号
//大舜丽池国际酒店8楼801');
//        $objPHPExcel->getActiveSheet()->mergeCells('A14:D14');
//        $objPHPExcel->getActiveSheet()->setCellValue('A14', '联系人：李天凤');
//        $objPHPExcel->getActiveSheet()->mergeCells('A15:D15');
//        $objPHPExcel->getActiveSheet()->setCellValue('A15', '联系电话：13202004505');
//        $objPHPExcel->getActiveSheet()->mergeCells('A17:D17');
//        $objPHPExcel->getActiveSheet()->setCellValue('A17', '结算日期：'.date('Y年m月d日', strtotime($bill['balance_time'])));
//
//        $objPHPExcel->getActiveSheet()->mergeCells('E9:G9');
//        $objPHPExcel->getActiveSheet()->setCellValue('E9', '乙方：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E10:G10');
//        $objPHPExcel->getActiveSheet()->setCellValue('E10', '盖章：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E11:G11');
//        $objPHPExcel->getActiveSheet()->setCellValue('E11', '联系人：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E12:G12');
//        $objPHPExcel->getActiveSheet()->setCellValue('E12', '联系电话：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E13:G13');
//        $objPHPExcel->getActiveSheet()->setCellValue('E13', '地址：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E14:G14');
//        $objPHPExcel->getActiveSheet()->setCellValue('E14', '请汇入此账号：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E15:G15');
//        $objPHPExcel->getActiveSheet()->setCellValue('E15', '户  名：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E16:G16');
//        $objPHPExcel->getActiveSheet()->setCellValue('E16', '帐  号：');
//        $objPHPExcel->getActiveSheet()->mergeCells('E17:G17');
//        $objPHPExcel->getActiveSheet()->setCellValue('E17', '开户行：');
//
//        $objPHPExcel->getActiveSheet()->mergeCells('A18:G18');
//        $objPHPExcel->getActiveSheet()->setCellValue('A18', '--本行以下无正文--');
//
//        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('7')->setRowHeight(30);
//        $objPHPExcel->getActiveSheet()->getRowDimension('8')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('11')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('12')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('13')->setRowHeight(34);
//        $objPHPExcel->getActiveSheet()->getRowDimension('14')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('15')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('16')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('17')->setRowHeight(28);
//        $objPHPExcel->getActiveSheet()->getRowDimension('18')->setRowHeight(28);
//
//        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
//        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);
//
//        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
//        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('G5')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('A13')->getFont()->setSize(12);
//        $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setSize(12);
//
//
//        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('C4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('D4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true);
//        $objPHPExcel->getActiveSheet()->getStyle('G5')->getFont()->setBold(true);
//
//        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('B4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('C4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('D4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('E4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('F4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('G4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('G5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('C6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('C7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getActiveSheet()->getStyle('E13')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//        $objActSheet->getColumnDimension('A')->setWidth(12);
//        $objActSheet->getColumnDimension('B')->setWidth(11);
//        $objActSheet->getColumnDimension('C')->setWidth(11);
//        $objActSheet->getColumnDimension('D')->setWidth(11);
//        $objActSheet->getColumnDimension('E')->setWidth(11);
//        $objActSheet->getColumnDimension('F')->setWidth(11);
//        $objActSheet->getColumnDimension('G')->setWidth(14);
//
//        $objPHPExcel->getActiveSheet()->getStyle('A1:G18')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$excelFileName.'.xlsx"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

}